﻿Imports WRDB.Project

Module modWRDBWeb

    Friend swDebug As IO.StreamWriter

    ''' <summary>
    ''' This is a utility routine that can be used to troubleshoot application deployment;
    ''' It creates and appends debug information to a file called debug.txt
    ''' </summary>
    ''' <param name="s"></param>
    ''' <remarks></remarks>
    Friend Sub writedebug(s As String)
        swDebug = New IO.StreamWriter(HttpContext.Current.Server.MapPath("~") & "\debug.txt", True)
        swDebug.WriteLine(Now.ToString("MM/dd/yy HH:mm:ss") & ": " & vbTab & s)
        swDebug.Close()
    End Sub

    Private _ProjectSettings As clsProjectSettings

    Friend Property ProjectSettings As clsProjectSettings
        Set(value As clsProjectSettings)
            _ProjectSettings = value
            _ProjectSettings.Save()
        End Set
        Get
            If _ProjectSettings Is Nothing Then
                _ProjectSettings = New clsProjectSettings
                _ProjectSettings.Load()
            End If
            Return _ProjectSettings
        End Get
    End Property

    ''' <summary>
    ''' Retrieve project from session object or create it based on ProjectName stored in settings
    ''' </summary>
    Friend ReadOnly Property Project As clsProject
        Get
            Dim _Project As clsProject = HttpContext.Current.Session("Project")
            If _Project Is Nothing Then
                With ProjectSettings
                    If String.IsNullOrEmpty(.ProjectName) Then Return Nothing
                    'try to create the project
                    Try
                        If .IsExternal Then
                            _Project = New WRDB.Project.clsProject(.ProjectName, True)
                            Dim ExtCon As New WRDB.External.clsExternalData(_Project, .ProjectName, .TableName)
                            If Not _Project.CreateTempProject(ExtCon.GetDB) Then Return Nothing
                        Else
                            _Project = New WRDB.Project.clsProject(.ProjectName)
                        End If
                    Catch ex As Exception
                        Return Nothing
                    End Try
                    _Project.DB.CommandTimeout = .Timeout
                    If Not (_Project.DB.TestConnectionQuick(5) AndAlso (_Project.DB.TableExists(.TableName) OrElse _Project.DB.ViewExists(.TableName))) Then Return Nothing
                End With
                HttpContext.Current.Session("Project") = _Project
                'if successful, fill the support tables that might be needed by reports
                Dim ds As New DataSet
                ds.Relations.Clear()
                ds.Tables.Clear()
                With _Project.DB
                    .AddTable(ds, "SelectedData", "SELECT * FROM {0} WHERE 'A'='B'", clsProject.TempData) 'schema only so can establish relations
                    If .TableExists("Stations") Then .AddTable(ds, "Stations") Else ds.Tables.Add(_Project.GetTableTemplate("Stations"))
                    If .TableExists("PCodes") Then .AddTable(ds, "PCodes") Else ds.Tables.Add(_Project.GetTableTemplate("PCodes").Clone)
                    If .TableExists("CCodes") Then .AddTable(ds, "CCodes") Else ds.Tables.Add(_Project.GetTableTemplate("CCodes").Clone)
                    If .TableExists("QCodes") Then .AddTable(ds, "QCodes") Else ds.Tables.Add(_Project.GetTableTemplate("QCodes").Clone)
                    If .TableExists("RCodes") Then .AddTable(ds, "RCodes") Else ds.Tables.Add(_Project.GetTableTemplate("RCodes").Clone)
                    If .TableExists("Criteria") Then .AddTable(ds, "Criteria") Else ds.Tables.Add(_Project.GetTableTemplate("Criteria").Clone)
                End With

                ProjectDataset = ds
                Return _Project
            Else
                Return _Project
            End If
        End Get
    End Property

    Friend Function ProjectImageURL(Project As WRDB.Project.clsProject) As String
        Dim imgURL As String = ""
        If Project IsNot Nothing AndAlso Not String.IsNullOrEmpty(Project.DesktopGraphicFile) Then
            Dim imgFile As String = HttpContext.Current.Server.MapPath("~") & "\Project Images\" & IO.Path.GetFileName(Project.DesktopGraphicFile)
            imgURL = "~\Project Images\" & IO.Path.GetFileName(Project.DesktopGraphicFile)
            If Not My.Computer.FileSystem.FileExists(imgFile) Then imgURL = ""
        End If
        Return imgURL
    End Function

    ''' <summary>
    ''' If the project is changed by the user, this will clear the session object and next time project is needed it will recreate it
    ''' </summary>
    Friend Sub ResetProject()
        HttpContext.Current.Session("Project") = Nothing
    End Sub

    Friend Property ProjectDataset As DataSet
        Set(ds As DataSet)
            HttpContext.Current.Session("ProjectDataset") = ds
        End Set
        Get
            If Project Is Nothing OrElse HttpContext.Current.Session("ProjectDataset") Is Nothing Then
                HttpContext.Current.Response.Redirect("Configure.aspx")
            End If
            Return HttpContext.Current.Session("ProjectDataset")
        End Get
    End Property

    Friend Property SelectedDataTable As DataTable
        Get
            Dim ds = ProjectDataset
            If ds IsNot Nothing AndAlso ds.Tables.Contains("SelectedData") Then
                Return ds.Tables("SelectedData")
            Else
                Return Nothing
            End If
        End Get
        Set(dt As DataTable)
            Dim ds As DataSet = ProjectDataset
            With ds
                .Relations.Clear()
                .Tables.Remove("SelectedData")
                dt.TableName = "SelectedData"
                .Tables.Add(dt)
                If .Tables.Contains("Stations") AndAlso .Tables.Contains("PCodes") AndAlso .Tables.Contains("CCodes") AndAlso .Tables.Contains("RCodes") AndAlso .Tables.Contains("QCodes") Then
                    .Relations.Add("Stations", .Tables("Stations").Columns("Station_ID"), .Tables("SelectedData").Columns("Station_ID"), False)
                    .Relations.Add("PCodes", .Tables("PCodes").Columns("PCode"), .Tables("SelectedData").Columns("PCode"), False)
                    .Relations.Add("CCodes", .Tables("CCodes").Columns("CCode"), .Tables("SelectedData").Columns("CCode"), False)
                    .Relations.Add("RCodes", .Tables("RCodes").Columns("RCode"), .Tables("SelectedData").Columns("RCode"), False)
                    .Relations.Add("QCodes", .Tables("QCodes").Columns("QCode"), .Tables("SelectedData").Columns("QCode"), False)
                End If
            End With
            ProjectDataset = ds
        End Set
    End Property

    Friend ShowMustSelectError As Boolean = False

    Friend Sub CheckSelectedData()
        If SelectedDataTable Is Nothing OrElse SelectedDataTable.Rows.Count = 0 Then
            ShowMustSelectError = True
            HttpContext.Current.Response.Redirect("SelectData.aspx")
        End If
    End Sub

    Friend ReadOnly Property NumTableRecords As String
        Get
            With ProjectSettings
                If .IsExternal Then
                    Return "(Unknown)"
                Else
                    If Project Is Nothing Then
                        Dim _Project As clsProject = HttpContext.Current.Session("Project")
                        If _Project Is Nothing Then _Project = New WRDB.Project.clsProject(.ProjectName)
                        _Project.DB.CommandTimeout = .Timeout
                        If Not _Project.DB.TestConnectionQuick(5) Then Throw _Project.DB.LastException
                    End If
                    Dim Num As Integer = Project.GetTrackingRecordCount(.TableName)
                    If Num = -1 Then Num = Project.GetRecordCount(.TableName)
                    Return String.Format("{0:N0}", Num)
                End If
            End With
        End Get
    End Property

    Friend ReadOnly Property NumSelectedRecords As String
        Get
            If SelectedDataTable Is Nothing Then
                Return "0"
            Else
                Return String.Format("{0:N0}", SelectedDataTable.Rows.Count)
            End If
        End Get
    End Property

    Friend Property LastSQL As String
        Get
            If Project Is Nothing OrElse HttpContext.Current.Session("LastSQL") Is Nothing Then
                HttpContext.Current.Response.Redirect("Configure.aspx")
            End If
            Return HttpContext.Current.Session("LastSQL")
        End Get
        Set(value As String)
            HttpContext.Current.Session("LastSQL") = value
        End Set
    End Property

    ''' <summary>
    ''' Provide string.split functionality where separator is not a character but is a string. 
    ''' Note: individual elements are trimmed of spaces
    ''' </summary>
    ''' <param name="StringToSplit">Original string</param>
    ''' <param name="SeparatorString">String to use as separator</param>
    ''' <param name="RemoveEmptyEntries">If true, will remove empty entries in array</param>
    ''' <returns>Array of components</returns>
    ''' <remarks>For example, if you want to split "A - B" call SplitString("A - B"," - ") to get A,B</remarks>
    Friend Function StringSplit(StringToSplit As String, SeparatorString As String, Optional RemoveEmptyEntries As Boolean = False) As String()
        Dim ar() As String = StringToSplit.Replace(SeparatorString, "`").Split(New Char() {"`"}, IIf(RemoveEmptyEntries, StringSplitOptions.RemoveEmptyEntries, StringSplitOptions.None))
        For i As Integer = 0 To ar.Length - 1
            ar(i) = ar(i).Trim
        Next
        Return ar
    End Function

    ''' <summary>
    ''' Test object; if dbnull, nothing, empty string, or minimum value, return default value
    ''' </summary>
    Friend Function TestNull(Value As Object, DefaultValue As Object) As Object
        If IsDBNull(Value) OrElse
            IsNothing(Value) OrElse
            (TypeOf Value Is String AndAlso String.IsNullOrEmpty(Value)) OrElse
            (TypeOf Value Is Date AndAlso Value = DateTime.MinValue) OrElse
            (IsNumeric(Value) AndAlso Value = Single.MinValue) Then Return DefaultValue Else Return Value
    End Function

#Region "Dialog Methods"

    ''' <summary>
    ''' This will register a script with an update panel that will run the next time a page update occurs
    ''' </summary>
    ''' <param name="upd">UpdatePanel that will contain the script</param>
    ''' <param name="script">The name of the JavaScript function to execute or JavaScript statement</param>
    ''' <remarks></remarks>
    Friend Sub RegisterScript(upd As UpdatePanel, script As String)
        ScriptManager.RegisterStartupScript(upd,
                                    GetType(Page),
                                    Guid.NewGuid.ToString,
                                    script,
                                    True)
    End Sub

    ''' <summary>
    ''' Show a jQuery dialog where the definition is stored within a specified Ajax Update Panel
    ''' </summary>
    ''' <param name="upd">Ajax update panel</param>
    ''' <param name="pnl">Panel containing content of dialog</param>
    ''' <param name="DialogTitle">Title to display on dialog titlebar</param>
    ''' <remarks>This works on partial postbacks</remarks>
    Friend Sub ShowDialog(upd As UpdatePanel, pnl As Panel, DialogTitle As String, width As Integer, Optional Resizable As Boolean = False)
        'the following forces the page to reposition itself *before* the dialog is displayed (otherwise it may scroll off the page)
        'however this does not work (for some reason) with the SelectData page, so I am removing it
        '"if (WebForm_RestoreScrollPosition) WebForm_RestoreScrollPosition();" &
        Dim js As String =
            "var dlg = $('[id$=""" & pnl.ID & """]').dialog({" &
            "         modal: true" &
            "       , autoOpen: true" &
            "       , appendTo: 'form'" &
            "       , title: '" & DialogTitle & "'" &
            "       , width: " & width &
            "       , height: 'auto'" &
            "       , closeOnEscape: true" &
            "       , draggable: true" &
            "       , resizable: " & IIf(Resizable, "true", "false") &
            "       }); " &
            "$('.ui-button-icon-only').hide();"
        'note: last line above hides the X close button on title bar (need to add Cancel button to all dialogs instead)
        'this was necessary because closing with X did not reinitialize report parameters properly and subsequent calls generated errors
        RegisterScript(upd, js)
    End Sub

    ''' <summary>
    ''' This is not really necessary because the page will display again and erase the dialog
    ''' </summary>
    ''' <param name="upd"></param>
    ''' <param name="pnl"></param>
    ''' <remarks></remarks>
    Friend Sub CloseDialog(upd As UpdatePanel, pnl As Panel)
        Dim js As String = String.Format("var dlg = $('[id$=""{0}""]')", pnl.ID) &
            ".dialog('close');"
        ScriptManager.RegisterStartupScript(upd,
                                            GetType(Page),
                                            Guid.NewGuid.ToString,
                                            js,
                                            True)
    End Sub

#End Region

    Public Function GetLoginName() As String
        Dim username As String = HttpContext.Current.Request.LogonUserIdentity.Name
        If username.Contains("\") Then username = username.Split("\")(1) 'strip off domain
        Return username
    End Function

End Module

''' <summary>
''' Project settings that are stored in the session and in cookies
''' </summary>
''' <remarks></remarks>
Friend Class clsProjectSettings
    Property ProjectName As String = ""
    Property TableName As String = ""
    Property Timeout As Integer = 30
    Property MaxRows As Long = 100000
    Property IsExternal As Boolean = False
    Property LocalQueries As New Dictionary(Of String, Dictionary(Of String, String)) 'saved for user in cookies
    Property GlobalQueries As New Dictionary(Of String, Dictionary(Of String, String)) 'shared by everyone in an xml file
    Property LastQuery As New Dictionary(Of String, String) 'last query run during this session
    Property Language As Globalization.CultureInfo = New Globalization.CultureInfo("es-CO")
    Property GraphStation As String = "" 'used when map calls graph routine
    Property GraphPCode As String = ""

    Sub New()
        InitializeWRDB()
    End Sub

    ''' <summary>
    ''' Set certain values required to run WRDB on a web server
    ''' </summary>
    Private Sub InitializeWRDB()
        'must set this so doesn't try to retrieve in MyDocuments which causes error
        WRDB.Project.clsApplicationSettings.SetTempAppDataPath(HttpContext.Current.Server.MapPath("~"))

        WRDB.DBProviders.clsDB.DLLPath = HttpContext.Current.Server.MapPath("~\bin")
        clsProject.TempPath = HttpContext.Current.Server.MapPath("~\Temp_Data")
        clsProject.TempData = clsProject.TempPrefix & "WRDBData"
        clsProject.TempView = String.Format("{0}{1}_WRDBView", clsProject.TempPrefix, clsApplicationSettings.UserName)
        clsProject.RptData = clsProject.DataPrefix & "WRDBRpt"
        clsProject.ExtData = clsProject.DataPrefix & "ExtData" 'stores data from external table

        'this provider may not support format of username for temp table--fix that

        'clsProject.TempData = Project.DB.GetValidTableName(clsProject.TempData)
        'clsProject.TempView = Project.DB.GetValidTableName(clsProject.TempView)
        'clsProject.RptData = Project.DB.GetValidTableName(clsProject.RptData)
        'clsProject.ExtData = Project.DB.GetValidTableName(clsProject.ExtData)
    End Sub

    ''' <summary>
    ''' Save all project settings in session and in cookies
    ''' </summary>
    Sub Save()
        Const MakeSessionCookie As Boolean = False 'of true, no cookie will persist and you will be prompted to configure (simulate new user)

        IsExternal = clsConnections.GetConnectionNames.Contains(ProjectName)

        'save as session variable...

        HttpContext.Current.Session("ProjectSettings") = Me

        'save as cookies...

        'clear all cookies for this app (do this by reset expiration to prior day)
        With HttpContext.Current.Request.Cookies
            For i As Integer = 0 To .Count - 1
                .Item(i).Expires = Now.AddDays(-1)
                HttpContext.Current.Response.Cookies.Add(.Item(i))
            Next
        End With

        Dim lstCookies As New List(Of HttpCookie)

        'selected project and table:

        lstCookies.Add(New HttpCookie("ProjectName", ProjectName))
        lstCookies.Add(New HttpCookie("TableName", TableName))
        lstCookies.Add(New HttpCookie("Timeout", Timeout))
        lstCookies.Add(New HttpCookie("MaxRows", MaxRows))
        lstCookies.Add(New HttpCookie("Language", Language.Name))

        'local queries (outside dictionary is query name, inside is field name->query string)

        'save list of fields and query strings
        For Each kvQuery In LocalQueries
            Dim c As New HttpCookie(kvQuery.Key)
            For Each kvField In kvQuery.Value
                If Not String.IsNullOrEmpty(kvField.Value) Then c.Values.Add(kvField.Key, kvField.Value)
            Next
            lstCookies.Add(c)
        Next

        'global queries (outside dictionary is query name, inside is field name->query string)

        'save in xml file
        Dim ds As New DataSet
        With ds.Tables.Add("GlobalQueries")
            .Columns.Add("QueryName")
            .PrimaryKey = {.Columns("QueryName")}
        End With
        With ds.Tables.Add("QueryFilters")
            .Columns.Add("QueryName")
            .Columns.Add("FieldName")
            .Columns.Add("Filter")
            .PrimaryKey = {.Columns("QueryName"), .Columns("FieldName")}
        End With

        For Each kvQuery In GlobalQueries
            ds.Tables("GlobalQueries").Rows.Add(kvQuery.Key)
            For Each kvField In kvQuery.Value
                If Not String.IsNullOrEmpty(kvField.Value) Then ds.Tables("QueryFilters").Rows.Add(kvQuery.Key, kvField.Key, kvField.Value)
            Next
        Next

        ds.WriteXml(HttpContext.Current.Server.MapPath("~") & "\GlobalQueries.xml")

        For Each c As HttpCookie In lstCookies
            If Not MakeSessionCookie Then c.Expires = Now.AddDays(30)
            HttpContext.Current.Response.Cookies.Add(c)
        Next

    End Sub

    ''' <summary>
    ''' Load project settings from session or cookies and create instance of global Project; if unable to do so or table was not set, returns False
    ''' </summary>
    Function Load() As Boolean

        'cannot only use cookies because there is a time delay when the Response cookie is set and when it is available as a Request cookie
        'therefore, assume the values are stored in the session state; if not found, then this is the first time it was requested and it is safe to get from cookie

        'set to True to clear all cookies to simulate new user
        If False Then HttpContext.Current.Request.Cookies.Clear()

        If HttpContext.Current.Session("ProjectSettings") Is Nothing Then 'first time; get cookies for all settings
            If HttpContext.Current.Request.Cookies.AllKeys.Contains("ProjectName") Then
                Try
                    ProjectName = HttpContext.Current.Request.Cookies("ProjectName").Value
                    TableName = HttpContext.Current.Request.Cookies("TableName").Value
                    Timeout = HttpContext.Current.Request.Cookies("Timeout").Value
                    MaxRows = HttpContext.Current.Request.Cookies("MaxRows").Value
                    Language = New Globalization.CultureInfo(HttpContext.Current.Request.Cookies("Language").Value)
                    Threading.Thread.CurrentThread.CurrentCulture = Globalization.CultureInfo.CreateSpecificCulture(Language.Name)
                    Threading.Thread.CurrentThread.CurrentUICulture = Language
                    IsExternal = clsConnections.GetConnectionNames.Contains(ProjectName)

                    'local queries...

                    LocalQueries.Clear()
                    For Each key As String In HttpContext.Current.Request.Cookies
                        Dim c As HttpCookie = HttpContext.Current.Request.Cookies(key)
                        If c.HasKeys Then
                            If Not LocalQueries.Keys.Contains(c.Name) Then LocalQueries.Add(c.Name, New Dictionary(Of String, String))
                            For Each field As String In c.Values.AllKeys
                                If Not LocalQueries(c.Name).ContainsKey(field) Then LocalQueries(c.Name).Add(field, c.Values(field))
                            Next
                        End If
                    Next

                Catch ex As Exception
                    Return False
                End Try
            End If

            'load global queries from xml file

            GlobalQueries.Clear()
            Dim ds As New DataSet
            Dim xmlFile As String = HttpContext.Current.Server.MapPath("~") & "\GlobalQueries.xml"
            If My.Computer.FileSystem.FileExists(xmlFile) Then
                ds.ReadXml(xmlFile)
                If ds.Tables.Contains("GlobalQueries") And ds.Tables.Contains("QueryFilters") Then
                    For Each dr As DataRow In ds.Tables("GlobalQueries").Rows
                        GlobalQueries.Add(dr("QueryName"), New Dictionary(Of String, String))
                        For Each drQuery As DataRow In ds.Tables("QueryFilters").Select(String.Format("QueryName='{0}'", dr("QueryName")))
                            With GlobalQueries(dr("QueryName"))
                                .Add(drQuery("FieldName"), drQuery("Filter"))
                            End With
                        Next
                    Next
                End If
            End If

        Else 'retrieve from session
            CopyFrom(HttpContext.Current.Session("ProjectSettings"))
        End If

        If String.IsNullOrEmpty(ProjectName) OrElse (Not clsConnections.ProjectExists(ProjectName) AndAlso Not clsConnections.GetConnectionNames.Contains(ProjectName)) Then Return False

        Return True
    End Function

    Function Clone() As clsProjectSettings
        Dim ps As New clsProjectSettings
        With ps
            .ProjectName = ProjectName
            .TableName = TableName
            .Timeout = Timeout
            .MaxRows = MaxRows
        End With
        Return ps
    End Function

    Sub CopyFrom(ps As clsProjectSettings)
        With ps
            ProjectName = .ProjectName
            TableName = .TableName
            Timeout = .Timeout
            MaxRows = .MaxRows
        End With
    End Sub

End Class



